package com.example.demo.config;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.example.demo.bean.Department;
import com.example.demo.bean.Employee;
import com.example.demo.bean.Equipment;


import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @program: SpringAOP
 * @ClassName EasyPOITest
 * @description:
 * @author: zsn
 * @create: 2024-07-22 10:14
 * @Version 1.0
 **/
public class ExportTest {

    public static void main(String[] args) throws IOException {

//        List<StudentEntity> list = new ArrayList<>();
//
//        StudentEntity stu = new StudentEntity("1", "张三", 2, new Date(), new Date());
//        list.add(stu);
//
//        Workbook Workbook = ExcelExportUtil.exportExcel(new ExportParams(), StudentEntity.class, list);
//
//        Workbook.write(new FileOutputStream(new File("D:\\"+"hahhah.xls")));
//
//        Workbook.close();
        test3();

    }

    public static void test1(){
        try {
            List<Map<String, Object>> list = new ArrayList<>();
            for (int i = 0; i < 2; i++) {
                HashMap<String, Object> map = new HashMap<>();
                map.put("id", i);
                map.put("name", "张三");
                map.put("sex", "男");
                map.put("birthday", new Date());
                map.put("registrationDate", new Date());
                map.put("excel_xh", i + 1);
                map.put("image","C:\\Users\\zsn\\Pictures\\Saved Pictures\\image.png");
                list.add(map);
            }
            ExcelWriter writer = ExcelUtil.getWriter(true);
            writer.setOnlyAlias(true);
            String[] column = new String[]{"id", "name", "sex", "birthday", "registrationDate", "image"};
            String[] header = new String[]{"id", "姓名", "性别", "出生日期", "进校日期","照片"};
            Integer[] columnWidth = new Integer[]{30, 30, 10, 30, 30,30};
            writer.addHeaderAlias("excel_xh", "序号");
            for (int i = 0; i < header.length; i++) {
                writer.addHeaderAlias(column[i], header[i]);
                writer.setColumnWidth(i + 1, columnWidth[i]);
            }
            writer.write(list, true);
            writer.flush(Files.newOutputStream(new File("D:\\" + "测试1.xls").toPath()));
        } catch (IOException e) {
            throw new RuntimeException(e);
        } finally {
        }
    }

    public static void test2(){
        List<Map<String, Object>> list = new ArrayList<>();
        for (int i = 0; i < 2; i++) {
            HashMap<String, Object> map = new HashMap<>();
            map.put("id", i);
            map.put("name", "张三");
            map.put("sex", "男");
            map.put("birthday", new Date());
            map.put("registrationDate", new Date());
            map.put("excel_xh", i + 1);
            list.add(map);
        }
        String[] column = new String[]{"id", "name", "sex", "birthday", "registrationDate"};
        String[] header = new String[]{"id", "姓名", "性别", "出生日期", "进校日期"};
        try {
            Workbook workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet("test");
            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直对齐
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//        cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
            cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
            cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
            cellStyle.setBorderRight(BorderStyle.THIN); //右边框
            cellStyle.setBorderTop(BorderStyle.THIN); //上边框

            XSSFFont headerFont = (XSSFFont) workbook.createFont(); // 创建字体样式
            headerFont.setBold(true); //字体加粗
            headerFont.setFontName("黑体"); // 设置字体类型
            headerFont.setFontHeightInPoints((short) 12); // 设置字体大小
            cellStyle.setFont(headerFont); // 为标题样式设置字体样式
            Row row0 = sheet.createRow(0);
            for (int i = 0; i < header.length; i++) {
                Cell cell = row0.createCell(i);
                cell.setCellValue(header[i]);
                cell.setCellStyle(cellStyle);
            }

            for (int i = 1; i <= list.size(); i++) {
                Row rowi = sheet.createRow(i);
                Map<String, Object> map = list.get(i - 1);
                for (int j = 0; j < column.length; j++) {
                    Cell cell = rowi.createCell(j);
                    cell.setCellStyle(cellStyle);
                    String key = column[j];
                    cell.setCellValue(map.get(key).toString());
                }
            }
            for (int i = 0; i < header.length; i++) {
                sheet.autoSizeColumn(i, true);
                sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
            }
            workbook.write(Files.newOutputStream(new File("D:\\" + "测试11.xls").toPath()));
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    // 复杂标题excel
    public static void test3() throws IOException {
        List<Map<String, Object>> list = new ArrayList<>();
        for (int i = 0; i < 2; i++) {
            HashMap<String, Object> map = new HashMap<>();
            map.put("bzName", "班组1");
            map.put("typeName", "安全帽");
            map.put("useCode", "001");
            map.put("returner", "张三");
            map.put("returnTime", "2025-02-11 08:46:00");
            map.put("returnCheck", "合格");
            map.put("taskName", "测试1");
            map.put("taskContent", "啊实打实大师");
            map.put("eleclevelName", "11v");
            map.put("excel_xh", String.valueOf(i + 1));
            map.put("recipient", "张三");
            map.put("useTime", "2025-02-10 08:46:00");
            list.add(map);
        }
        String[] column = new String[]{"excel_xh", "bzName", "typeName", "eleclevelName",
                "useCode", "taskName", "taskContent", "useTime", "recipient", "returnCheck", "returnTime", "returner"};
        String[] header1 = new String[]{"序号","班组名称", "安全工器具", "", "", "关联工作计划", "", "出库确认", "", "入库确认", "", ""};
        String[] header2 = new String[]{"", "", "名称", "电压等级", "编号", "作业名称", "作业内容", "领用时间", "领用人", "入库检查", "归还时间", "归还人"};
        /** 第一步，创建一个Workbook，对应一个Excel文件  */
        XSSFWorkbook wb = new XSSFWorkbook();
        /** 第二步，在Workbook中添加一个sheet,对应Excel文件中的sheet  */
        XSSFSheet sheet = wb.createSheet("excel导出标题");
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直对齐
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//        cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框
        int rowNum = 0;
        // 创建第一页的第一行，索引从0开始
        XSSFRow row0 = sheet.createRow(rowNum++);


        for (int i = 0; i < header1.length; i++) {
            XSSFCell c00 = row0.createCell(i);
            c00.setCellValue(header1[i]);
            c00.setCellStyle(cellStyle);
        }
        // 合并单元格，参数依次为起始列，结束列，起始行，结束行 （索引0开始）
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));//标题合并单元格操作，4为总列数
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));//标题合并单元格操作，4为总列数
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 2, 4));//标题合并单元格操作，4为总列数
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 5, 6));//标题合并单元格操作，4为总列数
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 7, 8));//标题合并单元格操作，4为总列数
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 9, 11));//标题合并单元格操作，4为总列数
        //第二行
        XSSFRow row2 = sheet.createRow(rowNum++);
        for (int i = 0; i < header2.length; i++) {
            XSSFCell tempCell = row2.createCell(i);
            tempCell.setCellValue(header2[i]);
            tempCell.setCellStyle(cellStyle);
        }
        CellStyle cellStyle1 = wb.createCellStyle();
        cellStyle1.setAlignment(HorizontalAlignment.CENTER); //水平居中
        cellStyle1.setVerticalAlignment(VerticalAlignment.CENTER); //垂直对齐
        for (Map<String, Object> map : list) {
            XSSFRow tempRow = sheet.createRow(rowNum++);
            for (int i1 = 0; i1 < column.length; i1++) {
                XSSFCell tempCell = tempRow.createCell(i1);
                tempCell.setCellStyle(cellStyle1);
                tempCell.setCellValue(map.get(column[i1]).toString());
            }
        }
        // 计算并设置列宽
        int[] maxWidths = new int[sheet.getRow(0).getLastCellNum()];
        for (Row r : sheet) {
            for (int i = 0; i < r.getLastCellNum(); i++) {
                Cell cell = r.getCell(i);
                String value = cell.getStringCellValue();
                int length = value.getBytes(StandardCharsets.UTF_8).length;
                if (length > maxWidths[i]) {
                    maxWidths[i] = length;
                }
            }
        }

        for (int i = 0; i < maxWidths.length; i++) {
            int width = (maxWidths[i] * 2 + 5) * 256;
            width = Math.min(width, 30 * 256); // 限制最大宽度
            sheet.setColumnWidth(i, width);
        }
        wb.write(Files.newOutputStream(new File("D:\\" + "测试mulHeader111.xls").toPath()));
    }

}




class Test2 {
    public static void main(String[] args) throws IOException {
        ArrayList<Equipment> equipments = new ArrayList<>();
        equipments.add(new Equipment("电脑"));
        equipments.add(new Equipment("手机"));
        equipments.add(new Equipment("路由器"));
        ArrayList<String> hobbys = new ArrayList<>();
        hobbys.add("唱歌");
        hobbys.add("跳舞");
        hobbys.add("篮球");
        Department department1 = new Department();
        department1.setId(1L);
        department1.setName("教学部");


        Department department2 = new Department();
        department2.setId(2L);
        department2.setName("IT部");


        //准备员工数据
        Employee e1 = new Employee();
        e1.setId(1L);
        e1.setUsername("张三");
        e1.setSex(false);
        e1.setEmail("zhang@qq.com");
        e1.setDepartment(department1);
        e1.setHeadImage("C:\\Users\\zsn\\Pictures\\Saved Pictures\\image.png");
        e1.setHobbys(hobbys);
        e1.setEquipments(equipments);

        Employee e2 = new Employee();
        e2.setId(2L);
        e2.setUsername("李四");
        e2.setSex(false);
        e2.setEmail("li@qq.com");
        e2.setDepartment(department2);
        e2.setHeadImage("C:\\Users\\zsn\\Pictures\\Saved Pictures\\image.png");
        e2.setHobbys(hobbys);
        e2.setEquipments(equipments);
        List<Employee> list = new ArrayList<>();
        list.add(e1);
        list.add(e2);

        /**
         * 进行相应的展出
         *  参数1:一些基本配置(表头等)
         *  参数2:导出的类型
         *  参数3:导出的数据
         */
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("员工信息", "员工数据"),
                Employee.class, list);

        //保存数据
        FileOutputStream fos = new FileOutputStream("D:\\emp3.xls");
        workbook.write(fos);
        fos.close();
    }
}